﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace RRD2.DAL
{
    public class Database
    {
        string connectionStringMain = ConfigurationManager.ConnectionStrings["SQLServerMain"].ToString();
        string connectionStringRFID = ConfigurationManager.ConnectionStrings["SQLServerRFID"].ToString();

        /*
        EXECUTE @RC = [Doc01].[dbo].[usp_GET_TrolleyStatusV2] 
   @vcTrolleyNumber
  ,@cClosed OUTPUT
  ,@iTrolleyID OUTPUT
*/

        internal bool TrolleyConfirm(string trolleyNumber)
        {
            bool value = false;
            try
            {
                using (var connection = new SqlConnection(connectionStringMain))
                {
                    var command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "usp_GET_TrolleyStatusV2";
                    command.CommandType = CommandType.StoredProcedure;

                    SqlParameter parameterTrolleyNumber = new SqlParameter();
                    parameterTrolleyNumber.ParameterName = "@trolleyNumber";
                    parameterTrolleyNumber.SqlDbType = SqlDbType.NVarChar;
                    parameterTrolleyNumber.Direction = ParameterDirection.Input;
                    parameterTrolleyNumber.Value = trolleyNumber;

                    SqlParameter parameterClosed = new SqlParameter();
                    parameterClosed.ParameterName = "@cClosed";
                    parameterClosed.SqlDbType = SqlDbType.Char;
                    parameterClosed.Direction = ParameterDirection.Output;

                    SqlParameter parameterTrolleyId = new SqlParameter();
                    parameterTrolleyId.ParameterName = "@iTrolleyID";
                    parameterTrolleyId.SqlDbType = SqlDbType.Int;
                    parameterTrolleyId.Direction = ParameterDirection.Output;

                    command.Parameters.Add(parameterTrolleyId);
                    command.Parameters.Add(parameterTrolleyNumber);
                    command.Parameters.Add(parameterClosed);

                    connection.Open();
                    command.ExecuteNonQuery();
                    if (!Convert.ToInt32(parameterTrolleyId.Value).Equals(0))
                    {
                        value = true;
                    }
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                Logger.WriteException(ex);
            }
            return value;
        }

        internal void WriteLog(Model.Log _log)
        {
            try
            {
                using (var connection = new SqlConnection(connectionStringRFID))
                {                    
                    var command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "webista_INS_Log";
                    command.CommandType = CommandType.StoredProcedure;

                    SqlParameter dateTimeParameter = new SqlParameter();
                    dateTimeParameter.ParameterName = "@dateTime";
                    dateTimeParameter.SqlDbType = SqlDbType.NVarChar;
                    dateTimeParameter.Direction = ParameterDirection.Input;
                    dateTimeParameter.Value = _log.DateTime;

                    SqlParameter statementParameter = new SqlParameter();
                    statementParameter.ParameterName = "@statement";
                    statementParameter.SqlDbType = SqlDbType.NVarChar;
                    statementParameter.Direction = ParameterDirection.Input;
                    statementParameter.Value = _log.Statement.Value;

                    SqlParameter statementTypeParameter = new SqlParameter();
                    statementTypeParameter.ParameterName = "@statementType";
                    statementTypeParameter.SqlDbType = SqlDbType.NVarChar;
                    statementTypeParameter.Direction = ParameterDirection.Input;
                    statementTypeParameter.Value = _log.Statement.Type;

                    SqlParameter statementbarcodeParameter = new SqlParameter();
                    statementbarcodeParameter.ParameterName = "@barcode";
                    statementbarcodeParameter.SqlDbType = SqlDbType.NVarChar;
                    statementbarcodeParameter.Direction = ParameterDirection.Input;
                    statementbarcodeParameter.Value = _log.Barcode;

                    SqlParameter statementUserParameter = new SqlParameter();
                    statementUserParameter.ParameterName = "@userId";
                    statementUserParameter.SqlDbType = SqlDbType.NVarChar;
                    statementUserParameter.Direction = ParameterDirection.Input;
                    statementUserParameter.Value = _log.UserId;

                    SqlParameter statementWorkstationParameter = new SqlParameter();
                    statementWorkstationParameter.ParameterName = "@workstationId";
                    statementWorkstationParameter.SqlDbType = SqlDbType.NVarChar;
                    statementWorkstationParameter.Direction = ParameterDirection.Input;
                    statementWorkstationParameter.Value = _log.WorkstationId;
                
                
                    command.Parameters.Add(dateTimeParameter);
                    command.Parameters.Add(statementParameter);
                    command.Parameters.Add(statementTypeParameter);
                    command.Parameters.Add(statementUserParameter);
                    command.Parameters.Add(statementWorkstationParameter);
                    command.Parameters.Add(statementbarcodeParameter);
                
                    connection.Open();
                    command.ExecuteNonQuery();                    
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                Logger.WriteException(ex);
            } 
        }
        
        /*
        EXEC	@return_value = [dbo].[usp_INS_TruckHeaderNew]
		@vcTruckName = N'L1234',
		@vcUpdatedBy = N'mdol',
		@biTruckLoadID = @biTruckLoadID OUTPUT  
          
        */

        internal int SetLorryHeader(string lorryName)
        {
            int value = 0;
            try
            {
                using (var connection = new SqlConnection(connectionStringMain))
                {
                    var command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "usp_INS_TruckHeaderNew";
                    command.CommandType = CommandType.StoredProcedure;

                    SqlParameter parameterUpdateBy = new SqlParameter();
                    parameterUpdateBy.ParameterName = "@vcUpdatedBy";
                    parameterUpdateBy.SqlDbType = SqlDbType.NVarChar;
                    parameterUpdateBy.Direction = ParameterDirection.Input;
                    parameterUpdateBy.Value = Environment.UserName;

                    SqlParameter parameterTruckNumber = new SqlParameter();
                    parameterTruckNumber.ParameterName = "@vcTruckName";
                    parameterTruckNumber.SqlDbType = SqlDbType.NVarChar;
                    parameterTruckNumber.Direction = ParameterDirection.Input;
                    parameterTruckNumber.Value = lorryName;

                    SqlParameter parameterLorryLoadId = new SqlParameter();
                    parameterLorryLoadId.ParameterName = "@biTruckLoadID";
                    parameterLorryLoadId.SqlDbType = SqlDbType.BigInt;
                    parameterLorryLoadId.Direction = ParameterDirection.Output;

                    command.Parameters.Add(parameterUpdateBy);
                    command.Parameters.Add(parameterTruckNumber);
                    command.Parameters.Add(parameterLorryLoadId);

                    connection.Open();
                    command.ExecuteNonQuery();
                    value = Convert.ToInt32(parameterLorryLoadId.Value);
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                Logger.WriteException(ex);
            }
            return value;
        }


        /*
         EXEC	@return_value = [dbo].[usp_INS_TruckDetailNew]
		@vcTrolleyNumber = N'T1234',
		@vcUpdatedBy = N'mdol',
		@biTruckLoadID = 2
         */

        internal bool SetLorryDetail(string _trolleyNumber, int _lorryLoadId)
        {
            bool value = false;
            try
            {
                using (var connection = new SqlConnection(connectionStringMain))
                {
                    var command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "usp_INS_TruckDetailNew";
                    command.CommandType = CommandType.StoredProcedure;

                    SqlParameter parameterTrolleyNumber = new SqlParameter();
                    parameterTrolleyNumber.ParameterName = "@vcTrolleyNumber";
                    parameterTrolleyNumber.SqlDbType = SqlDbType.NVarChar;
                    parameterTrolleyNumber.Direction = ParameterDirection.Input;
                    parameterTrolleyNumber.Value = _trolleyNumber;

                    SqlParameter parameterUpdateBy = new SqlParameter();
                    parameterUpdateBy.ParameterName = "@vcUpdatedBy";
                    parameterUpdateBy.SqlDbType = SqlDbType.NVarChar;
                    parameterUpdateBy.Direction = ParameterDirection.Input;
                    parameterUpdateBy.Value = Environment.UserName;

                    SqlParameter parameterLorryLoadId = new SqlParameter();
                    parameterLorryLoadId.ParameterName = "@biTruckLoadID";
                    parameterLorryLoadId.SqlDbType = SqlDbType.BigInt;
                    parameterLorryLoadId.Direction = ParameterDirection.Input;
                    parameterLorryLoadId.Value = _lorryLoadId;

                    command.Parameters.Add(parameterTrolleyNumber);
                    command.Parameters.Add(parameterUpdateBy);
                    command.Parameters.Add(parameterLorryLoadId);

                    connection.Open();
                    if(command.ExecuteNonQuery().Equals(-1))
                        value=true;
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                Logger.WriteException(ex);
            }
            return value;
        }

        /*
         *EXEC	@return_value = [dbo].[usp_UPD_TruckHeaderClosedNew]
		@vcUpdatedBy = N'mdol',
		@biTruckLoadID = 1
         */
        internal bool UpdateLorryHeader(int _lorryLoadId)
        {
            bool value = false;
            try
            {
                using (var connection = new SqlConnection(connectionStringMain))
                {
                    var command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "usp_UPD_TruckHeaderClosedNew";
                    command.CommandType = CommandType.StoredProcedure;
                    
                    SqlParameter parameterUpdateBy = new SqlParameter();
                    parameterUpdateBy.ParameterName = "@vcUpdatedBy";
                    parameterUpdateBy.SqlDbType = SqlDbType.NVarChar;
                    parameterUpdateBy.Direction = ParameterDirection.Input;
                    parameterUpdateBy.Value = Environment.UserName;

                    SqlParameter parameterLorryLoadId = new SqlParameter();
                    parameterLorryLoadId.ParameterName = "@biTruckLoadID";
                    parameterLorryLoadId.SqlDbType = SqlDbType.BigInt;
                    parameterLorryLoadId.Direction = ParameterDirection.Input;
                    parameterLorryLoadId.Value = _lorryLoadId;

                    command.Parameters.Add(parameterUpdateBy);
                    command.Parameters.Add(parameterLorryLoadId);

                    connection.Open();
                    int c = command.ExecuteNonQuery();
                    if (c.Equals(-1))
                        value = true;
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                Logger.WriteException(ex);
            }
            return value;
        }

    }
}
